﻿namespace HuaWei.CommClass
{
    using System;
    using System.Text;

    public static class CSQL
    {
        public static string _str = "\r\nselect a.*,b.bglh,[c.jls] as jls,a.bgzjl - [c.jls] as sysl from BGLQD a left join BGL b on a.pid=b.id \r\nleft join (\r\nselect bglqdid,sum(bqjls) as jls from bgqdjl_mx where bdid='{0}' group by bglqdid\r\n) c on a.id=c.bglqdid\r\n where a.bdid='{0}'\r\n";
        public static string _str1 = @"select b.id,b.pid,b.qdbh,qdmc,dw,htsl,zbdj,htje,sgtsl,sgtje,sgtyx,a.bqmjls,a.bqmjls*zbdj as bqmjlje,
                                        case when bghsl=0 then 0 else a.bqmjls/bghsl*100 end as bl from 
                                        (select qdbh,sum(bqjls) as bqmjls from qdjl_mx where bdid='{0}' and qcid<=10 group by qdbh) a
                                         right join 
                                        (select id,pid,qdbh,qdmc,qdlx,htsl,htje,zbdj,0 as lx,dw,sgtsl,sgtje,sgtyx,case when sgtyx=1 then sgtsl else htsl end as bghsl from jlqd where bdid='{0}') b
                                        on a.qdbh=b.qdbh
                                         left join 
                                        (select qdbh,sum(bqjls) as sqjls from qdjl_mx where bdid='{0}' and qcid<=10-1 group by qdbh) d
                                        on a.qdbh=d.qdbh
                                         left join 
                                        (select qdbh,sum(bqjls) as bqjls from qdjl_mx where bdid='{0}' and qcid=10 group by qdbh) e
                                        on a.qdbh=e.qdbh  order by qdbh";
        //public static string BGJLQCHZ = "\r\nselect a.qdbh,a.qdmc,a.HTSL,[a.htsl]+[b.bgzjl] as BGHSL,b.BGZJL,c.SQJL,d.SQMJL,e.BQMJL,f.BQJL,a.ZKCJBL from \r\n(select qdmc,qdbh,htsl,zkcjbl from JLQD where qdbh='{0}') as a \r\nleft join \r\n(select qdbh,sum([bgzjl]) as BGZJL from BGLQD where bdid='{1}' and pid in (select id from BGL where bdid='{1}') \r\nand qdbh='{0}' group by qdbh) as b\r\n on a.qdbh=b.qdbh\r\nleft join\r\n(select sum([bqjls]) as SQJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid={2}-1\r\nand qdbh='{0}' group by qdbh) as c  on a.qdbh=c.qdbh\r\nleft join \r\n(select sum([bqjls]) as SQMJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid<{2}\r\nand qdbh='{0}' group by qdbh) as d on a.qdbh=d.qdbh\r\nleft join \r\n(select sum([bqjls]) as BQMJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid<={2}\r\nand qdbh='{0}' group by qdbh) as e on a.qdbh=e.qdbh\r\nleft join \r\n(select sum([bqjls]) as BQJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid={2}\r\nand qdbh='{0}' group by qdbh) as f on a.qdbh=f.qdbh";
        public static string BGJLQCHZ = "select a.qdbh,a.qdmc,0 as HTSL,0 as BGHSL,a.BGZJL,c.SQJL,d.SQMJL,e.BQMJL,f.BQJL,0 as ZKCJBL from (select qdbh,qdmc,sum(case when isnull(bgzjl)=1 then 0 else bgzjl end) as BGZJL from BGLQD where bdid='{1}' and pid in (select id from BGL where bdid='{1}') and qdbh='{0}' group by qdbh,qdmc) as a left join (select sum(case when isnull(bqjls)=1 then 0 else bqjls end) as SQJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid={2}-1 and qdbh='{0}' group by qdbh) as c  on a.qdbh=c.qdbh left join (select sum(case when isnull(bqjls)=1 then 0 else bqjls end) as SQMJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid<{2} and qdbh='{0}' group by qdbh) as d on a.qdbh=d.qdbh left join (select sum(case when isnull(bqjls)=1 then 0 else bqjls end) as BQMJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid<={2} and qdbh='{0}' group by qdbh) as e on a.qdbh=e.qdbh left join (select sum(case when isnull(bqjls)=1 then 0 else bqjls end) as BQJL,qdbh from BGQDJL_MX where bdid='{1}' and qcid={2} and qdbh='{0}' group by qdbh) as f on a.qdbh=f.qdbh";
        public static string BGJLQD = "\r\n            select * from JLQD where id in (\r\n            select qdid from BGLQD where bdid='#bdid#'\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select qdid from BGLQD where bdid='#bdid#')\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select qdid from BGLQD where bdid='#bdid#'))\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select qdid from BGLQD where bdid='#bdid#'))))";
        public static string BGLQCHZ = "\r\nselect aa.*,convert(decimal(18,2),aa.bgzjl/bb.htsl*100.00) as bl,bb.htsl,qdbh,qdmc,dw from (\r\n            select 0 as bgtype,'' + b.bglh as mc,qdid,bgzjl as bgzjl from BGLQD a left join bgl b on a.pid=b.id where a.bdid='#bdid#' and b.bglxh<'#bglxh#'\r\n            union                \r\n            select 1 as bgtype,'以上增减小计' as mc,qdid,sum(bgzjl) as bgzjl from BGLQD where bdid='#bdid#' and pid in (select id from bgl where bdid='#bdid#' and bglxh<'#bglxh#') group by qdid\r\n            union\r\n            select 2 as bgtype,'本次变更增减' as mc,qdid,sum(bgzjl) as bgzjl from BGLQD where bdid='#bdid#' and pid in (select id from bgl where  bdid='#bdid#' and bglxh='#bglxh#') group by qdid\r\n            union\r\n            select 3 as bgtype,'所有增长累计' as mc,qdid,sum(bgzjl) as bgzjl from BGLQD where bdid='#bdid#' and pid in (select id from bgl where bdid='#bdid#' and bglxh<='#bglxh#') group by qdid\r\n            ) aa left join JLQD bb on aa.qdid=bb.id";
        public static string BGQDJL_M_CT_V = "\r\n            (SELECT     a.ID, a.XMID, a.BDID, a.HVS, a.QQZH, a.TZBH, a.BW, a.ZJJGZSBH, a.JLCT, a.JSBL, a.PZBH, b.CT, a.YZID, a.JLID, a.QCID\r\n             FROM         BGQDJL_M AS a LEFT OUTER JOIN\r\n             GCLCT AS b ON a.JLCT = b.CTLJ) a";
        public static string BGSP_V = "\r\n            (SELECT     B.PZBH, B.QQZH, B.BW, B.TZBH, B.ZJJGZSBH, A.ID, A.PID, A.XMID, A.YZID, A.JLID, A.BDID, A.QCID, A.QDID, A.QDBH, A.QDMC, A.DW, A.BQJLS, \r\n             A.HVS\r\n            FROM BGQDJL_MX AS A LEFT OUTER JOIN BGQDJL_M AS B ON A.PID = B.ID) a";
        public static string BGSPQCQD = "\r\n            select * from JLQD where id in (\r\n            select qdid from BGQDJL_MX where bdid='#bdid#' and qcid='#qcid#'\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select qdid from BGQDJL_MX where bdid='#bdid#' and qcid='#qcid#')\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select qdid from BGQDJL_MX where bdid='#bdid#' and qcid='#qcid#'))\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select qdid from BGQDJL_MX where bdid='#bdid#' and qcid='#qcid#'))))";
        public static string JLQCHZ = "\r\nselect a.qdbh,a.qdmc,a.HTSL,[a.htsl]+[b.bgzjl] as BGHSL,b.BGZJL,c.SQJL,d.SQMJL,e.BQMJL,f.BQJL,a.ZKCJBL from \r\n(select qdmc,qdbh,htsl,zkcjbl from JLQD where qdbh='{0}') as a \r\nleft join \r\n(select qdbh,sum([bgzjl]) as BGZJL from BGLQD where bdid='{1}' and pid in (select id from BGL where bdid='{1}' and BGLRQ<='{3}') \r\nand qdbh='{0}' group by qdbh) as b\r\n on a.qdbh=b.qdbh\r\nleft join\r\n(select sum([bqjls]) as SQJL,qdbh from QDJL_MX where bdid='{1}' and qcid={2}-1\r\nand qdbh='{0}' group by qdbh) as c  on a.qdbh=c.qdbh\r\nleft join \r\n(select sum([bqjls]) as SQMJL,qdbh from QDJL_MX where bdid='{1}' and qcid<{2}\r\nand qdbh='{0}' group by qdbh) as d on a.qdbh=d.qdbh\r\nleft join \r\n(select sum([bqjls]) as BQMJL,qdbh from QDJL_MX where bdid='{1}' and qcid<={2}\r\nand qdbh='{0}' group by qdbh) as e on a.qdbh=e.qdbh\r\nleft join \r\n(select sum([bqjls]) as BQJL,qdbh from QDJL_MX where bdid='{1}' and qcid={2}\r\nand qdbh='{0}' group by qdbh) as f on a.qdbh=f.qdbh";
        public static string JLQCHZ_back = "             \r\n            select 1 as bz,'原合同数量' as mc,htsl as sl,qdbh,ZKCJBL as cjbl from JLQD where bdid='#bdid#' and qdbh in (              \r\n            select qdbh from #JLMX# where bdid='#bdid#' and qcid=#qcid#)\r\n            union all\r\n            select 2 as bz,'变更后数量' as mc,sum(sl) as sl,qdbh,0 as cjbl from (\r\n            select htsl as sl,id as qdbh from JLQD where bdid='#bdid#' and qdbh in (              \r\n            select qdbh from #JLMX# where bdid='#bdid#' and qcid=#qcid#)\r\n            union all\r\n            select bgzjl as sl,qdbh from BGLQD where bdid='#bdid#' and qdbh in (              \r\n            select qdbh from #JLMX# where bdid='#bdid#' and qcid=#qcid#) \r\n            ) aa group by qdbh\r\n            union all\r\n            select 3 as bz,'变更增减数量' as mc,sum(bgzjl) as sl,qdbh,0 as cjbl from BGLQD where bdid='#bdid#' and qdbh in (              \r\n            select qdbh from #JLMX# where bdid='#bdid#' and qcid=#qcid#)\r\n            group by qdbh\r\n            union all\r\n            select 4 as bz,'上期计量' as mc,sum(bqjls) as sl,qdbh,0 as cjbl from #JLMX# where bdid='#bdid#' and qcid='#qcid#-1'\r\n            group by qdbh\r\n            union all\r\n            select 5 as bz,'上期末计量' as mc,sum(bqjls) as sl,qdbh,0 as cjbl from #JLMX# where bdid='#bdid#' and qcid<'#qcid#'\r\n            group by qdbh\r\n            union all\r\n            select 6 as bz,'本期末计量' as mc,sum(bqjls) as sl,qdbh,0 as cjbl from #JLMX# where bdid='#bdid#' and qcid<='#qcid#'\r\n            group by qdbh";
        public static string JLQCHZ_QDBH = "\r\n            select 1 as bz,'原合同数量' as mc,htsl as sl,qdbh,ZKCJBL as cjbl from JLQD where bdid='#bdid#' and qdbh='#qdbh#' \r\n            union all\r\n            select 2 as bz,'变更后数量' as mc,sum(sl) as sl,qdbh,0 as cjbl from (\r\n            select htsl as sl,qdbh from JLQD where bdid='#bdid#' and qdbh='#qdbh#'\r\n            union all\r\n            select bgzjl as sl,qdbh from BGLQD where bdid='#bdid#' and qdbh='#qdbh#'\r\n            ) aa group by qdbh\r\n            union all\r\n            select 3 as bz,'变更增减数量' as mc,sum(bgzjl) as sl,qdbh,0 as cjbl from BGLQD where bdid='#bdid#' and qdbh='#qdbh#'\r\n            group by qdbh";
        public static string JLQCHZ_QDBHIsAll = "\r\n            select 1 as bz,'原合同数量' as mc,htsl as sl,qdbh,ZKCJBL as cjbl from JLQD where bdid='#bdid#' and qdbh='#qdbh#'\r\n            union all\r\n            select 2 as bz,'变更后数量' as mc,sum(sl) as sl,qdbh,0 as cjbl from (\r\n            select htsl as sl,qdbh from JLQD where bdid='#bdid#' and qdbh='#qdbh#'\r\n            union all\r\n            select bgzjl as sl,qdbh from BGLQD where bdid='#bdid#' and qdbh='#qdbh#'\r\n            ) aa group by qdbh\r\n            union all\r\n            select 3 as bz,'变更增减数量' as mc,sum(bgzjl) as sl,qdbh,0 as cjbl from BGLQD where bdid='#bdid#' and qdbh='#qdbh#'\r\n            group by qdbh\r\n            union all\r\n            select 4 as bz,'上期计量' as mc,sum(bqjls) as sl,qdbh,0 as cjbl from #JLMX# where bdid='#bdid#' and qdbh='#qdbh#'\r\n             and qcid=#qcid#-1 group by qdbh\r\n            union all\r\n            select 5 as bz,'上期末计量' as mc,sum(bqjls) as sl,qdbh,0 as cjbl from #JLMX# where bdid='#bdid#' and qdbh='#qdbh#' and qcid<'#qcid#'\r\n            group by qdbh\r\n            union all\r\n            select 6 as bz,'本期末计量' as mc,0 as sl,'#qdbh#' as qdbh,0 as cjbl";
        public static string JLQDBGWCQuery = "\r\nselect b.id,b.pid,b.qdbh,qdmc,dw,htsl+[c.bgs] as htsl,zbdj,(htsl+[c.bgs])*zbdj as htje,a.bqmjls,a.bqmjls*zbdj as bqmjlje,a.bqmjls/(htsl+[c.bgs])*100 as bl from \r\n(select qdbh,sum(bqjls) as bqmjls from qdjl_mx where bdid='{0}' and qcid<={1} group by qdbh) a\r\nleft join \r\n(select qdbh,sum(BGZJL) as bgs from BGLQD where bdid='{0}' group by qdbh) c\r\non a.qdbh=c.qdbh\r\n right join \r\n(select id,pid,qdbh,qdmc,qdlx,htsl,zbdj,0 as lx,dw from jlqd where bdid='{0}') b\r\non a.qdbh=b.qdbh\r\n left join \r\n(select qdbh,sum(bqjls) as sqjls from qdjl_mx where bdid='{0}' and qcid<={1}-1 group by qdbh) d\r\non a.qdbh=d.qdbh\r\n left join \r\n(select qdbh,sum(bqjls) as bqjls from qdjl_mx where bdid='{0}' and qcid={1} group by qdbh) e\r\non a.qdbh=e.qdbh";
        public static string JLSP_V = "\r\n            (SELECT     B.PZBH, B.QQZH, B.BW, B.TZBH, B.ZJJGZSBH, A.ID, A.PID, A.XMID, A.YZID, A.JLID, A.BDID, A.QCID, A.QDID, A.QDBH, A.QDMC, A.DW, A.BQJLS, \r\n            A.HVS\r\n            FROM  QDJL_MX AS A LEFT OUTER JOIN\r\n                      QDJL_M AS B ON A.PID = B.ID) a";
        public static string JLSPQCQD = "\r\n            select * from JLQD where id in (\r\n            select qdid from QDJL_MX where bdid='{0}' and qcid='{1}'\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select qdid from QDJL_MX where bdid='{0}' and qcid='{1}')\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select qdid from QDJL_MX where bdid='{0}' and qcid='{1}'))\r\n            union all\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select pid from JLQD where id in (\r\n            select qdid from QDJL_MX where bdid='{0}' and qcid='{1}')))) order by qdbh";
        public static string Q_JLTZ = "select * from (\r\n(select * from JLQD where xmid='ac7a9e3e-54f7-4b87-abc9-4a41651385cc' \r\nand bdid='071b5625-6f0e-4831-a0db-d6d375d4e212' ) a\r\nleft join\r\n\r\n(select qdid,sum(bgzjje) as je from BGLQD  where xmid='ac7a9e3e-54f7-4b87-abc9-4a41651385cc' \r\nand bdid='472966ac-2714-4814-9e9b-02ef79153e1f' group by qdid) b \r\non  a.qdid=b.qdid\r\nleft join\r\n(select qdid,qcid,sum(yzsps) as sl from qdjl_mx  where xmid='ac7a9e3e-54f7-4b87-abc9-4a41651385cc' \r\nand bdid='472966ac-2714-4814-9e9b-02ef79153e1f' group by qdid,qcid) c\r\non  a.qdid=a.qdid\r\n)";
        public static string QDJL_M_CT_V = "\r\n            (SELECT     a.ID, a.XMID, a.BDID, a.HVS, a.QQZH, a.TZBH, a.BW, a.ZJJGZSBH, a.JLCT, a.JSBL, a.PZBH, b.CT, a.YZID, a.JLID, a.QCID\r\n            FROM         QDJL_M AS a LEFT OUTER JOIN\r\n            GCLCT AS b ON a.JLCT = b.CTLJ) a";
        public static string TREE_JG = "\r\n            #SELECT_ALL# where id='#ID#'\r\n            union all\r\n            #SELECT_ALL# where pid='#ID#'\r\n            union all\r\n            #SELECT_ALL# where pid in (#SELECT_ID# where pid='#ID#')\r\n            union all\r\n            #SELECT_ALL# where pid in (#SELECT_ID# where pid in (#SELECT_ID# where pid='#ID#'))\r\n            union all\r\n            #SELECT_ALL# where pid in (#SELECT_ID# where pid in (#SELECT_ID# where pid in (#SELECT_ID# where pid='#ID#')))\r\n            union all\r\n            #SELECT_ALL# where id in (#SELECT_PID# where id='#ID#')\r\n            union all\r\n            #SELECT_ALL# where id in (#SELECT_PID# where id in (#SELECT_PID# where id='#ID#'))\r\n            union all\r\n            #SELECT_ALL# where id in (#SELECT_PID# where id in (#SELECT_PID# where id in (#SELECT_PID# where id='#ID#')))\r\n            union all\r\n            #SELECT_ALL# where id in (#SELECT_PID# where id in (#SELECT_PID# where id in (#SELECT_PID# where id in (#SELECT_PID# where id='#ID#'))))";
        public static string ZHQuery = "\r\nselect *,htje+bgzjje as bghje,yzspje from (\r\n\r\nselect a.qcid,b.htje,\r\ncase when isnull(c.bgzjje)=1 then 0 else c.bgzjje end as bgzjje,\r\ncase when isnull(d.yzspje)=1 then 0 else d.yzspje end as yzspje from \r\n\r\n--select * from\r\n(\r\n\r\nselect 1 as tag,qcid  from xmqc where bdid='9703932c-2a36-4a47-a2e8-332f5b0c3f0b' group by qcid\r\n) as a\r\n left join \r\n(\r\nselect 1 as tag,sum(htje) as htje from JLQD where bdid='76d9de13-0368-4478-9208-c383e8b7139a'\r\n) as b on a.tag=b.tag \r\nleft join\r\n(\r\nselect qcid,sum(bgzjje) as bgzjje from BGLQD where bdid='76d9de13-0368-4478-9208-c383e8b7139a' group by qcid\r\n) as c on a.qcid=c.qcid\r\nleft join\r\n(\r\n\tselect qcid,sum(yzspje) as yzspje from \r\n\t(\r\n\t  select qcid,a1.yzsps*b1.zbdj as yzspje from qdjl_mx a1 left join (select qdbh,zbdj from JLQD where bdid='76d9de13-0368-4478-9208-c383e8b7139a') b1\r\n\t  on a1.qdbh=b1.qdbh\r\n\t  where yzspzt=0 and bdid='76d9de13-0368-4478-9208-c383e8b7139a'\r\n\t ) aa group by qcid\r\n\r\n) as d on a.qcid=d.qcid\r\n\r\n) bb";
        public static string ZZJGXX_V = "\r\n            select * from ZZJGXX where id='#ID#'\r\n            union all\r\n            select * from ZZJGXX where pid='#ID#'\r\n            union all\r\n            select * from ZZJGXX where pid in (select id from ZZJGXX where pid='#ID#')\r\n            union all\r\n            select * from ZZJGXX where pid in (select id from ZZJGXX where pid in (select id from ZZJGXX where pid='#ID#'))\r\n            union all\r\n            select * from ZZJGXX where pid in (select id from ZZJGXX where pid in (select id from ZZJGXX where pid in (select id from ZZJGXX where pid='#ID#')))\r\n            union all\r\n            select * from ZZJGXX where id in (select pid from ZZJGXX where id='#ID#')\r\n            union all\r\n            select * from ZZJGXX where id in (select pid from ZZJGXX where id in (select pid from ZZJGXX where id='#ID#'))\r\n            union all\r\n            select * from ZZJGXX where id in (select pid from ZZJGXX where id in (select pid from ZZJGXX where id in (select pid from ZZJGXX where id='#ID#')))\r\n            union all\r\n            select * from ZZJGXX where id in (select pid from ZZJGXX where id in (select pid from ZZJGXX where id in (select pid from ZZJGXX where id in (select pid from ZZJGXX where id='#ID#'))))\r\n            ";

        public static string smethod(string xmid, string bdid, string qcid, string qdbh)
        {
            string str = "";
            StringBuilder builder = new StringBuilder();
            str = "select a.*,case when b.yxsl=0 then 0 else a.sl/(b.yxsl+[c.bgzjl])*100.000000 end as BL,b.yxsl as htsl,c.bgzjl,b.zkcjbl from (";
            builder.Append(str);
            str = "select 1 as xh,qdbh,'第'+ qcid + '期完成' as MC,sum([BQJLS]) as SL from qdjl_mx \r\n                   where bdid='#bdid#'\r\n                   and qcid<'#qcid#'\r\n                   and qdbh='#qdbh#' \r\n                   group by qdbh,qcid ";
            builder.Append(str);
            builder.Append(" union ");
            str = "select 2 as xh,qdbh,'本期完成' as MC,sum([BQJLS]) as SL from qdjl_mx \r\n                   where bdid='#bdid#'\r\n                   and qcid='#qcid#'\r\n                   and qdbh='#qdbh#' \r\n                   group by qdbh,qcid ";
            builder.Append(str);
            builder.Append(" union ");
            str = "select 3 as xh,qdbh,'本期末完成' as MC,sum([BQJLS]) as SL from qdjl_mx \r\n                   where bdid='#bdid#'\r\n                   and convert(decimal(10,0),qcid)<='#qcid#'\r\n                   and qdbh='#qdbh#' \r\n                   group by qdbh ";
            builder.Append(str);
            builder.Append(" union ");
            str = "select -4 as xh,qdbh,'合同数量' as MC,htsl as SL from jlqd \r\n                   where bdid='#bdid#'\r\n                   and qdbh='#qdbh#'";
            builder.Append(str);
            builder.Append(" union ");
            str = "select -3 as xh,qdbh,'施工图数量' as MC,sgtsl as SL from jlqd \r\n                   where bdid='#bdid#'\r\n                   and qdbh='#qdbh#'";
            builder.Append(str);
            builder.Append(" union ");
            str = "select -2 as xh,qdbh,'变更数量' as MC,sum([BGZJL]) as SL from BGLQD\r\n                        where bdid='#bdid#' and qdbh='#qdbh#'\r\n                        and pid in (select id from bgl ) group by qdbh";
            builder.Append(str);
            builder.Append(" union ");
            str = "\r\n                select -1 as xh,qdbh,'更变后数量' as MC,sum([SL]) as SL from (\r\n                select qdbh,case when sgtyx=1 then sgtsl else htsl end as SL from jlqd \r\n                    where bdid='#bdid#' and qdbh='#qdbh#'\r\n                union \r\n                select qdbh,sum([BGZJL]) as SL from BGLQD\r\n                    where bdid='#bdid#'  and qdbh='#qdbh#'\r\n                    and pid in (select id from bgl) group by qdbh\r\n                ) bgh group by qdbh";
            builder.Append(str);
            str = ") a left join (select *,case when sgtyx=1 then sgtsl else htsl end as yxsl from jlqd) b on a.qdbh=b.qdbh and b.bdid='#bdid#'";
            builder.Append(str);
            str = " left join (select qdbh,sum([BGZJL]) as bgzjl from BGLQD\r\n                where bdid='#bdid#'\r\n                and pid in (select id from bgl) group by qdbh) c on a.qdbh=c.qdbh";
            builder.Append(str);
            return CHelp.smethod(builder.ToString().Replace("#bdid#", bdid).Replace("#qcid#", qcid).Replace("#qdbh#", qdbh));
        }
    }
}

